using Microsoft.AspNetCore.Mvc;
using SPI.AgentServer.Services.DatabaseLearning;
using SPI.AgentServer.Services.AI;
using Swashbuckle.AspNetCore.Annotations;

namespace SPI.AgentServer.Controllers;

/// <summary>
/// 数据库学习控制器 - 让 AI 理解数据库结构
/// </summary>
[ApiController]
[Route("api/learning")]
[Produces("application/json")]
public class LearningController : ControllerBase
{
    private readonly IDatabaseSchemaLearner _schemaLearner;
    private readonly IPromptManager _promptManager;
    private readonly ILogger<LearningController> _logger;

    public LearningController(
        IDatabaseSchemaLearner schemaLearner,
        IPromptManager promptManager,
        ILogger<LearningController> logger)
    {
        _schemaLearner = schemaLearner;
        _promptManager = promptManager;
        _logger = logger;
    }

    /// <summary>
    /// 让 AI 学习数据库结构
    /// </summary>
    /// <remarks>
    /// 这个接口会分析数据库中的所有表结构，生成自然语言描述，
    /// 让 AI 模型理解数据库的组织方式和业务含义。
    /// 建议在首次使用或数据库结构变更后调用。
    /// </remarks>
    /// <param name="databaseName">数据库名称（可选，默认使用配置的默认数据库）</param>
    /// <param name="saveToFile">是否保存到文件</param>
    /// <returns>学习结果</returns>
    [HttpPost("learn-schema")]
    [SwaggerOperation(
        Summary = "学习数据库结构",
        Description = "分析数据库表结构并生成 AI 可理解的描述",
        OperationId = "LearnDatabaseSchema",
        Tags = new[] { "数据库学习" }
    )]
    [ProducesResponseType(typeof(SchemaLearningResponse), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status500InternalServerError)]
    public async Task<IActionResult> LearnSchema(
        [FromQuery] string? databaseName = null,
        [FromQuery] bool saveToFile = true)
    {
        try
        {
            _logger.LogInformation("开始学习数据库结构，数据库：{Database}", databaseName ?? "默认");
            
            // 生成数据库描述
            var description = await _schemaLearner.GenerateSchemaDescriptionAsync(databaseName);
            
            // 重要：将数据库结构缓存到 PromptManager
            // 这样每次查询时会自动包含数据库结构信息
            _promptManager.SetSchemaDescription(description);
            _logger.LogInformation("数据库结构已缓存到内存，后续查询将自动包含此信息");
            
            // 保存到文件（可选）
            string? savedFile = null;
            if (saveToFile)
            {
                var fileName = $"schema_{databaseName ?? "default"}_{DateTime.Now:yyyyMMdd_HHmmss}.txt";
                await _schemaLearner.SaveSchemaDescriptionAsync(description, fileName);
                savedFile = fileName;
            }

            // 统计信息
            var lines = description.Split('\n');
            var tableCount = lines.Count(l => l.StartsWith("### 表："));
            var fieldCount = lines.Count(l => l.Trim().StartsWith("- ") && l.Contains("("));
            
            var response = new SchemaLearningResponse
            {
                Success = true,
                Message = "数据库结构学习完成",
                DatabaseName = databaseName ?? "默认",
                TableCount = tableCount,
                FieldCount = fieldCount,
                DescriptionLength = description.Length,
                SavedToFile = savedFile,
                Description = description,
                Timestamp = DateTime.Now
            };

            _logger.LogInformation(
                "数据库结构学习完成：{Tables} 个表，{Fields} 个字段，生成 {Length} 字符描述",
                tableCount, fieldCount, description.Length);

            return Ok(response);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "学习数据库结构时发生错误");
            return StatusCode(500, new 
            {
                success = false,
                message = $"学习失败：{ex.Message}",
                error = ex.ToString()
            });
        }
    }

    /// <summary>
    /// 获取已学习的数据库描述
    /// </summary>
    /// <param name="fileName">文件名（可选，默认获取最新的）</param>
    /// <returns>数据库描述内容</returns>
    [HttpGet("schema-description")]
    [SwaggerOperation(
        Summary = "获取数据库描述",
        Description = "获取之前学习生成的数据库结构描述",
        OperationId = "GetSchemaDescription",
        Tags = new[] { "数据库学习" }
    )]
    [ProducesResponseType(typeof(string), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<IActionResult> GetSchemaDescription([FromQuery] string? fileName = null)
    {
        try
        {
            var schemaDocsPath = "schema_docs";
            
            if (!Directory.Exists(schemaDocsPath))
            {
                return NotFound(new { message = "没有找到学习记录" });
            }

            string filePath;
            if (string.IsNullOrEmpty(fileName))
            {
                // 获取最新的文件
                var files = Directory.GetFiles(schemaDocsPath, "*.txt")
                    .OrderByDescending(f => new FileInfo(f).CreationTime)
                    .FirstOrDefault();
                
                if (files == null)
                {
                    return NotFound(new { message = "没有找到学习记录" });
                }
                
                filePath = files;
            }
            else
            {
                filePath = Path.Combine(schemaDocsPath, fileName);
                if (!System.IO.File.Exists(filePath))
                {
                    return NotFound(new { message = $"文件 {fileName} 不存在" });
                }
            }

            var content = await System.IO.File.ReadAllTextAsync(filePath);
            return Ok(new
            {
                fileName = Path.GetFileName(filePath),
                content = content,
                createdAt = new FileInfo(filePath).CreationTime
            });
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取数据库描述时发生错误");
            return StatusCode(500, new { message = $"获取失败：{ex.Message}" });
        }
    }

    /// <summary>
    /// 检查当前的记忆状态
    /// </summary>
    /// <remarks>
    /// 显示系统是否已经缓存了数据库结构，以及缓存的内容
    /// </remarks>
    /// <returns>记忆状态信息</returns>
    [HttpGet("memory-status")]
    [SwaggerOperation(
        Summary = "检查记忆状态",
        Description = "查看系统是否记住了数据库结构",
        OperationId = "CheckMemoryStatus",
        Tags = new[] { "数据库学习" }
    )]
    [ProducesResponseType(typeof(MemoryStatusResponse), StatusCodes.Status200OK)]
    public IActionResult GetMemoryStatus()
    {
        var cachedSchema = _promptManager.GetCachedSchemaDescription();
        var hasMemory = !string.IsNullOrEmpty(cachedSchema);
        
        var response = new MemoryStatusResponse
        {
            HasMemory = hasMemory,
            Message = hasMemory 
                ? "✅ 系统已记住数据库结构，无需重复学习" 
                : "⚠️ 系统还没有学习数据库结构，请先执行学习",
            SchemaLength = cachedSchema?.Length ?? 0,
            Preview = hasMemory ? cachedSchema!.Substring(0, Math.Min(500, cachedSchema!.Length)) + "..." : null,
            Recommendation = hasMemory 
                ? "可以直接开始查询，系统会自动使用缓存的数据库结构" 
                : "请先调用 POST /api/learning/learn-schema 学习数据库结构"
        };
        
        return Ok(response);
    }

    /// <summary>
    /// 清除缓存的数据库结构
    /// </summary>
    [HttpDelete("clear-memory")]
    [SwaggerOperation(
        Summary = "清除记忆",
        Description = "清除缓存的数据库结构，需要重新学习",
        OperationId = "ClearMemory",
        Tags = new[] { "数据库学习" }
    )]
    public IActionResult ClearMemory()
    {
        _promptManager.SetSchemaDescription("");
        return Ok(new 
        {
            success = true,
            message = "已清除数据库结构缓存"
        });
    }

    /// <summary>
    /// 测试数据库连接
    /// </summary>
    [HttpGet("test-connection")]
    [SwaggerOperation(
        Summary = "测试数据库连接",
        Description = "验证数据库连接是否正常",
        OperationId = "TestDatabaseConnection",
        Tags = new[] { "数据库学习" }
    )]
    public Task<IActionResult> TestConnection([FromQuery] string? databaseName = null)
    {
        try
        {
            // 这里可以调用 DatabaseExecutor 的测试方法
            // 暂时返回简单的成功消息
            return Task.FromResult<IActionResult>(Ok(new
            {
                success = true,
                message = "数据库连接成功",
                database = databaseName ?? "默认",
                timestamp = DateTime.Now
            }));
        }
        catch (Exception ex)
        {
            return Task.FromResult<IActionResult>(StatusCode(500, new
            {
                success = false,
                message = $"连接失败：{ex.Message}"
            }));
        }
    }
    
    /// <summary>
    /// 学习特定的数据库表
    /// </summary>
    /// <param name="request">包含要学习的特定表列表</param>
    /// <returns>学习结果</returns>
    [HttpPost("learn-tables")]
    [SwaggerOperation(
        Summary = "学习特定表",
        Description = "让AI学习指定的数据库表结构，而不是整个数据库",
        OperationId = "LearnSpecificTables",
        Tags = new[] { "数据库学习" }
    )]
    [ProducesResponseType(typeof(object), StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status500InternalServerError)]
    public async Task<IActionResult> LearnSpecificTables([FromBody] LearnSpecificTablesRequest request)
    {
        if (request.SpecificTables == null || request.SpecificTables.Count == 0)
        {
            return BadRequest(new { message = "请指定要学习的表" });
        }
        
        try
        {
            _logger.LogInformation($"开始学习特定表：{string.Join(", ", request.SpecificTables)}");
            
            // 获取特定表的结构
            var schemaDescription = await _schemaLearner.LearnSpecificTablesAsync(request.Database, request.SpecificTables);
            
            // 缓存到内存
            _promptManager.SetSchemaDescription(schemaDescription);
            
            _logger.LogInformation($"成功学习 {request.SpecificTables.Count} 个表，总大小：{schemaDescription.Length} 字符");
            
            return Ok(new
            {
                success = true,
                message = $"成功学习 {request.SpecificTables.Count} 个表的结构",
                tablesLearned = request.SpecificTables,
                schemaSize = schemaDescription.Length
            });
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "学习特定表失败");
            return StatusCode(500, new
            {
                success = false,
                message = $"学习失败：{ex.Message}"
            });
        }
    }
}

/// <summary>
/// 记忆状态响应模型
/// </summary>
public class MemoryStatusResponse
{
    public bool HasMemory { get; set; }
    public string Message { get; set; } = string.Empty;
    public int SchemaLength { get; set; }
    public string? Preview { get; set; }
    public string Recommendation { get; set; } = string.Empty;
}

/// <summary>
/// 数据库学习响应模型
/// </summary>
public class SchemaLearningResponse
{
    public bool Success { get; set; }
    public string Message { get; set; } = string.Empty;
    public string DatabaseName { get; set; } = string.Empty;
    public int TableCount { get; set; }
    public int FieldCount { get; set; }
    public int DescriptionLength { get; set; }
    public string? SavedToFile { get; set; }
    public string Description { get; set; } = string.Empty;
    public DateTime Timestamp { get; set; }
}

/// <summary>
/// 学习特定表的请求模型
/// </summary>
public class LearnSpecificTablesRequest
{
    /// <summary>
    /// 数据库名称
    /// </summary>
    public string Database { get; set; } = "Test";
    
    /// <summary>
    /// 要学习的特定表列表
    /// </summary>
    public List<string> SpecificTables { get; set; } = new();
}
